# Implementing custom time dimension granularities

This recipe show examples of commonly used [custom
granularities][ref-custom-granularities].

## Use case

Sometimes, you might need to group the result set by units of time that are
different from [default granularities][ref-default-granularities] such as `week`
(starting on Monday) or `year` (starting on January 1).

Below, we explore the following examples of custom granularities:
* *Week starting on Sunday*, commonly used in the US and some other countries.
* *[Fiscal year][wiki-fiscal-year]* and *fiscal quarter*, commonly used in
accounting and financial reporting.

## Data modeling

Consider the following data model. `interval` and `offset` parameters are used to
configure each custom granularity in `granularities`.

Note that custom granularities are also exposed via [proxy
dimensions][ref-proxy-granularity] so that we can conveniently query them via
[Playground][ref-playground] or BI tools connected via the [SQL API][ref-sql-api].
We can also use them in further calculations like rendering `fiscal_quarter_label`.

<CodeTabs>

```yaml
cubes:
  - name: custom_granularities
    sql: >
      SELECT '2024-01-15'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-02-15'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-03-15'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-04-15'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-05-15'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-06-15'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-07-15'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-08-15'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-09-15'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-10-15'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-11-15'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-12-15'::TIMESTAMP AS timestamp

    dimensions:
      - name: timestamp
        sql: timestamp
        type: time

        granularities:
          - name: sunday_week
            interval: 1 week
            offset: -1 day

          - name: fiscal_year
            title: Federal fiscal year in the United States
            interval: 1 year
            offset: -3 months

          - name: fiscal_quarter
            title: Federal fiscal quarter in the United States
            interval: 1 quarter
            offset: -3 months
      
      - name: sunday_week
        sql: "{timestamp.sunday_week}"
        type: time
      
      - name: fiscal_year
        sql: "{timestamp.fiscal_year}"
        type: time
      
      - name: fiscal_quarter
        sql: "{timestamp.fiscal_quarter}"
        type: time

      - name: fiscal_quarter_label
        sql: >
          'FY' || (EXTRACT(YEAR FROM {timestamp.fiscal_year}) + 1) ||
          '-Q' || EXTRACT(QUARTER FROM {timestamp.fiscal_quarter} + INTERVAL '3 MONTHS')
        type: string
```

```javascript
cube(`custom_granularities`, {
  sql: `
    SELECT '2024-01-15'::TIMESTAMP AS timestamp UNION ALL
    SELECT '2024-02-15'::TIMESTAMP AS timestamp UNION ALL
    SELECT '2024-03-15'::TIMESTAMP AS timestamp UNION ALL
    SELECT '2024-04-15'::TIMESTAMP AS timestamp UNION ALL
    SELECT '2024-05-15'::TIMESTAMP AS timestamp UNION ALL
    SELECT '2024-06-15'::TIMESTAMP AS timestamp UNION ALL
    SELECT '2024-07-15'::TIMESTAMP AS timestamp UNION ALL
    SELECT '2024-08-15'::TIMESTAMP AS timestamp UNION ALL
    SELECT '2024-09-15'::TIMESTAMP AS timestamp UNION ALL
    SELECT '2024-10-15'::TIMESTAMP AS timestamp UNION ALL
    SELECT '2024-11-15'::TIMESTAMP AS timestamp UNION ALL
    SELECT '2024-12-15'::TIMESTAMP AS timestamp
  `,

  dimensions: {
    timestamp: {
      sql: `timestamp`,
      type: `time`,

      granularities: {
        sunday_week: {
          interval: `1 week`,
          offset: `-1 day`
        },

        fiscal_year: {
          title: `Federal fiscal year in the United States`,
          interval: `1 year`,
          offset: `-3 months`
        },

        fiscal_quarter: {
          title: `Federal fiscal quarter in the United States`,
          interval: `1 quarter`,
          offset: `-3 months`
        }
      }
    },
    
    sunday_week: {
      sql: `${timestamp.sunday_week}`,
      type: `time`
    },

    fiscal_year: {
      sql: `${timestamp.fiscal_year}`,
      type: `time`
    },
      
    fiscal_quarter: {
      sql: `${timestamp.fiscal_quarter}`,
      type: `time`
    },

    fiscal_quarter_label: {
      sql: `
          'FY' || (EXTRACT(YEAR FROM {timestamp.fiscal_year}) + 1) ||
          '-Q' || EXTRACT(QUARTER FROM {timestamp.fiscal_quarter} + INTERVAL '3 MONTHS')
      `,
      type: `string`
    }
  }
})
```

</CodeTabs>

## Result

Querying this data modal would yield the following result:

<Screenshot src="https://ucarecdn.com/c385c29d-e145-47da-8551-2be5efb1c268/"/>


[ref-custom-granularities]: /reference/data-model/dimensions#granularities
[ref-default-granularities]: /product/data-modeling/concepts#time-dimensions
[wiki-fiscal-year]: https://en.wikipedia.org/wiki/Fiscal_year
[ref-playground]: /product/workspace/playground
[ref-sql-api]: /product/apis-integrations/sql-api
[ref-proxy-granularity]: /product/data-modeling/concepts/calculated-members#time-dimension-granularity